Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Multiblock Reads

When performing table scans, Oracle has the ability to read more than one block at a time, thus speeding up I/O. By reading more than one block at a time, Oracle reads a larger block from the disk and eliminates some disk seeks. By reducing disk seeks and reading larger blocks, both I/O and CPU overhead are reduced.

This feature is called multiblock reads. Multiblock reads are beneficial but take advantage of only contiguous blocks. Blocks in an extent are always contiguous. If your data is in many small extents, the effect of multiblock reads is reduced.

The amount of data read in a multiblock read is specified by the Oracle initialization parameter, DB_FILE_MULTIBLOCK_READ_COUNT. The value for this parameter should always be set high because there is rarely any disadvantage in doing so. The size of the I/Os depends on both DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE.

To take advantage of multiblock reads, you should try to configure your system so that the database blocks are as contiguous as possible. To do this, you should try to create your database with optimally sized extents.

Creating these extents may not be a straightforward process, however. By creating extents too large, Oracle may have a difficult time finding enough contiguous space to create these extents. On the other hand, creating extents too small not only adversely affects multiblock reads, it also causes more dynamic extensions. Knowing what your initial data and growth patterns will be may help in sizing your extents.

Multiblock Writes

New in Oracle 7.3 is the multiblock writes feature. Multiblock writes are similar to multiblock reads and have many of the same requirements. Under certain conditions, you can perform multiblock writes:

  Multiblock writes are available through the direct path loader as well as through sorts and index creations. As with multiblock reads, multiblock writes reduce I/O and CPU overhead by writing multiple database blocks in one larger I/O operation.
  The amount of data written in a multiblock write is specified by the Oracle initialization parameter, DB_FILE_MULTIBLOCK_WRITE_COUNT. The size of the I/Os depends on both DB_FILE_MULTIBLOCK_READ_COUNT and DB_BLOCK_SIZE.

Parallel Query Option

The Oracle Parallel Query option makes it possible for some Oracle functions to be processed by multiple server processes. These functions are queries, index creation, data loading, and recovery. In each of these functions, the general principle is the same: keep the processing going while Oracle is waiting for I/O.

For most queries, the time spent waiting for the data to be retrieved from disk usually overshadows the amount of time actually spent processing the results. With the Parallel Query option, you can compensate for this by using several server processes to execute the query. While one process is waiting for I/Os to complete, other processes can be executing. If you are running on a Symmetric Multiprocessor (SMP) computer, a cluster, or an Massively Parallel Processing (MPP) machine, you can take maximum advantage of the Parallel Query option.

Many processes working together can simultaneously process a single SQL statement, a situation known as parallel query processing. The other functions are known as parallel index creation, parallel loading, and parallel recovery, each of which is discussed in the following sections.

Parallel Query Processing

Parallel Query Processing allows certain Oracle statements to be run in parallel by multiple server processes. The Oracle server can process the following statements in parallel:

  SELECT statements
  Subqueries in UPDATE and DELETE statements
  CREATE TABLE tablename AS SELECT statements
  CREATE INDEX statements

Parallel queries are effective on large operations such as table scans and sorts.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.